(*
Copyright (c) 2008 Handel Information Technologies, Inc.

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated
documentation files (the "Software"), to deal in the Software without restriction, including without limitation
the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software,
and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions
of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
IN THE SOFTWARE.
*)
#light

namespace SQLGen
open System.Text.RegularExpressions;;

module util = begin
  let rec zip l1 l2 =
    match l1 with
    | [] -> []
    | it1 :: l1p ->
      match l2 with
      | [] -> []
      | it2 :: l2p -> (it1, it2) :: zip l1p l2p
end

exception IllegalStatementType of string

type Table = struct
    val Database : string option
    val Owner : string option
    val TableName : string
    val Alias : string option
    static member Name (tableName : string) = Table(None, None, tableName, None)
    static member NameAs ((tableName : string), (alias : string)) = Table(None, None, tableName, Some(alias))
    static member QualifiedName ((database : string), (owner : string), (tableName : string)) = Table(Some(database), Some(owner), tableName, None)
    static member QualifiedNameAs ((database : string), (owner : string), (tableName : string), (alias : string)) = Table(Some(database), Some(owner), tableName, Some(alias))
    new(database, owner, tableName, alias) =
        { Database = database;
          Owner = owner;
          TableName = tableName;
          Alias = alias }
end

type Rel = struct
    val parentTable : Table
    val parentField : string
    val childTable : Table
    val childField : string
    new(pTable, pField, cTable, cField) =
        { parentTable = pTable;
          parentField = pField;
          childTable = cTable;
          childField = cField }
  end

type Val =
  | Field of string option * string
  | Parameter of string
  | ValExpression of string
  | StringLiteral of string
  | IntLiteral of int
  | FloatLiteral of float
  | BoolLiteral of bool
  | DateLiteral of System.DateTime
  | DateTimeLiteral of System.DateTime
  with 
    member x.EQ (v : Val) = Comparison(x, Equals(v))
    member x.EQFld (fieldName : string) = Comparison(x, Equals(Field(None, fieldName)))
    member x.EQFldT ((tableName : string), (fieldName : string)) = Comparison(x, Equals(Field(Some(tableName), fieldName)))
    member x.EQParam (parameterName : string) = Comparison(x, Equals(Parameter(parameterName)))
    member x.EQExpr (expression : string) = Comparison(x, Equals(ValExpression(expression)))
    member x.EQStr (literal : string) = Comparison(x, Equals(StringLiteral(literal)))
    member x.EQInt (literal : int) = Comparison(x, Equals(IntLiteral(literal)))
    member x.EQFloat (literal : float) = Comparison(x, Equals(FloatLiteral(literal)))
    member x.EQBool (literal : bool) = Comparison(x, Equals(BoolLiteral(literal)))
    member x.EQDate (literal : System.DateTime) = Comparison(x, Equals(DateLiteral(literal)))
    member x.EQDateTime (literal : System.DateTime) = Comparison(x, Equals(DateTimeLiteral(literal)))
    member x.NE (v : Val) = Comparison(x, NotEqual(v))
    member x.NEFld (fieldName : string) = Comparison(x, NotEqual(Field(None, fieldName)))
    member x.NEFldT ((tableName : string), (fieldName : string)) = Comparison(x, NotEqual(Field(Some(tableName), fieldName)))
    member x.NEParam (parameterName : string) = Comparison(x, NotEqual(Parameter(parameterName)))
    member x.NEExpr (expression : string) = Comparison(x, NotEqual(ValExpression(expression)))
    member x.NEStr (literal : string) = Comparison(x, NotEqual(StringLiteral(literal)))
    member x.NEInt (literal : int) = Comparison(x, NotEqual(IntLiteral(literal)))
    member x.NEFloat (literal : float) = Comparison(x, NotEqual(FloatLiteral(literal)))
    member x.NEBool (literal : bool) = Comparison(x, NotEqual(BoolLiteral(literal)))
    member x.NEDate (literal : System.DateTime) = Comparison(x, NotEqual(DateLiteral(literal)))
    member x.NEDateTime (literal : System.DateTime) = Comparison(x, NotEqual(DateTimeLiteral(literal)))
    member x.LT (v : Val) = Comparison(x, LessThan(v))
    member x.LTFld (fieldName : string) = Comparison(x, LessThan(Field(None, fieldName)))
    member x.LTFldT ((tableName : string), (fieldName : string)) = Comparison(x, LessThan(Field(Some(tableName), fieldName)))
    member x.LTParam (parameterName : string) = Comparison(x, LessThan(Parameter(parameterName)))
    member x.LTExpr (expression : string) = Comparison(x, LessThan(ValExpression(expression)))
    member x.LTStr (literal : string) = Comparison(x, LessThan(StringLiteral(literal)))
    member x.LTInt (literal : int) = Comparison(x, LessThan(IntLiteral(literal)))
    member x.LTFloat (literal : float) = Comparison(x, LessThan(FloatLiteral(literal)))
    member x.LTBool (literal : bool) = Comparison(x, LessThan(BoolLiteral(literal)))
    member x.LTDate (literal : System.DateTime) = Comparison(x, LessThan(DateLiteral(literal)))
    member x.LTDateTime (literal : System.DateTime) = Comparison(x, LessThan(DateTimeLiteral(literal)))
    member x.LE (v : Val) = Comparison(x, LessThanOrEqual(v))
    member x.LEFld (fieldName : string) = Comparison(x, LessThanOrEqual(Field(None, fieldName)))
    member x.LEFldT ((tableName : string), (fieldName : string)) = Comparison(x, LessThanOrEqual(Field(Some(tableName), fieldName)))
    member x.LEParam (parameterName : string) = Comparison(x, LessThanOrEqual(Parameter(parameterName)))
    member x.LEExpr (expression : string) = Comparison(x, LessThanOrEqual(ValExpression(expression)))
    member x.LEStr (literal : string) = Comparison(x, LessThanOrEqual(StringLiteral(literal)))
    member x.LEInt (literal : int) = Comparison(x, LessThanOrEqual(IntLiteral(literal)))
    member x.LEFloat (literal : float) = Comparison(x, LessThanOrEqual(FloatLiteral(literal)))
    member x.LEBool (literal : bool) = Comparison(x, LessThanOrEqual(BoolLiteral(literal)))
    member x.LEDate (literal : System.DateTime) = Comparison(x, LessThanOrEqual(DateLiteral(literal)))
    member x.LEDateTime (literal : System.DateTime) = Comparison(x, LessThanOrEqual(DateTimeLiteral(literal)))
    member x.GT (v : Val) = Comparison(x, GreaterThan(v))
    member x.GTFld (fieldName : string) = Comparison(x, GreaterThan(Field(None, fieldName)))
    member x.GTFldT ((tableName : string), (fieldName : string)) = Comparison(x, GreaterThan(Field(Some(tableName), fieldName)))
    member x.GTParam (parameterName : string) = Comparison(x, GreaterThan(Parameter(parameterName)))
    member x.GTExpr (expression : string) = Comparison(x, GreaterThan(ValExpression(expression)))
    member x.GTStr (literal : string) = Comparison(x, GreaterThan(StringLiteral(literal)))
    member x.GTInt (literal : int) = Comparison(x, GreaterThan(IntLiteral(literal)))
    member x.GTFloat (literal : float) = Comparison(x, GreaterThan(FloatLiteral(literal)))
    member x.GTBool (literal : bool) = Comparison(x, GreaterThan(BoolLiteral(literal)))
    member x.GTDate (literal : System.DateTime) = Comparison(x, GreaterThan(DateLiteral(literal)))
    member x.GTDateTime (literal : System.DateTime) = Comparison(x, GreaterThan(DateTimeLiteral(literal)))
    member x.GE (v : Val) = Comparison(x, GreaterThanOrEqual(v))
    member x.GEFld (fieldName : string) = Comparison(x, GreaterThanOrEqual(Field(None, fieldName)))
    member x.GEFldT ((tableName : string), (fieldName : string)) = Comparison(x, GreaterThanOrEqual(Field(Some(tableName), fieldName)))
    member x.GEParam (parameterName : string) = Comparison(x, GreaterThanOrEqual(Parameter(parameterName)))
    member x.GEExpr (expression : string) = Comparison(x, GreaterThanOrEqual(ValExpression(expression)))
    member x.GEStr (literal : string) = Comparison(x, GreaterThanOrEqual(StringLiteral(literal)))
    member x.GEInt (literal : int) = Comparison(x, Equals(IntLiteral(literal)))
    member x.GEFloat (literal : float) = Comparison(x, GreaterThanOrEqual(FloatLiteral(literal)))
    member x.GEBool (literal : bool) = Comparison(x, GreaterThanOrEqual(BoolLiteral(literal)))
    member x.GEDate (literal : System.DateTime) = Comparison(x, GreaterThanOrEqual(DateLiteral(literal)))
    member x.GEDateTime (literal : System.DateTime) = Comparison(x, GreaterThanOrEqual(DateTimeLiteral(literal)))
    member x.Like (v : Val) = Comparison(x, Like(v))
    member x.LikeFld (fieldName : string) = Comparison(x, Like(Field(None, fieldName)))
    member x.LikeFldT ((tableName : string), (fieldName : string)) = Comparison(x, Like(Field(Some(tableName), fieldName)))
    member x.LikeParam (parameterName : string) = Comparison(x, Like(Parameter(parameterName)))
    member x.LikeExpr (expression : string) = Comparison(x, Like(ValExpression(expression)))
    member x.LikeStr (literal : string) = Comparison(x, Like(StringLiteral(literal)))
    member x.IsNull = Comparison(x, IsNull)
    member x.IsNotNull = Comparison(x, IsNotNull)
  end
and Comparator =
  | Equals of Val
  | NotEqual of Val
  | LessThan of Val
  | LessThanOrEqual of Val
  | GreaterThan of Val
  | GreaterThanOrEqual of Val
  | Like of Val
  | IsNull
  | IsNotNull
and Where =
  | All
  | FieldValueList of string * int array
  | SubQueryIn of Rel * Where
  | SubQueryExists of Rel * Where
  | And of Where * Where
  | Or of Where * Where
  | Comparison of Val * Comparator
  | SelExpression of string
  | Not of Where
  with
    member x.And sel =
      if x = All
        then sel
        else
          if sel = All
            then x
            else And(x, sel)
    member x.Or sel = 
      if x = All
        then All              (* (true) or (sel) -> true *)
        else
          if sel = All
            then sel          (*  (x) or (true) -> true  *)
            else Or(x, sel)
    static member Fld (fieldName : string) = Field(None, fieldName)
    static member FldT ((tableName : string), (fieldName : string)) = Field(Some(tableName), fieldName)
    static member Param (parameterName : string) = Parameter(parameterName)
    static member Expr (expression : string) = ValExpression(expression)
    static member Str (stringLiteral : string) = StringLiteral(stringLiteral)
    static member Int (intLiteral : int) = IntLiteral(intLiteral)
    static member Float (floatLiteral : float) = FloatLiteral(floatLiteral)
    static member Date (dateLiteral : System.DateTime) = DateLiteral(dateLiteral)
    static member DateTime (dateTimeLiteral : System.DateTime) = DateTimeLiteral(dateTimeLiteral)
  end

type JoinType =
  | Inner
  | Left
  | Right
  | Full

type From = 
  | NoTable
  | Simple of Table
  | JoinWith of From * JoinType * Rel 
  with
    member x.Join ((joinType : JoinType), (relationship : Rel)) = JoinWith(x, joinType, relationship)
    member x.InnerJoin (relationship : Rel) = JoinWith(x, Inner, relationship)
    member x.InnerJoin ((tableName : string), (sourceField : string), (targetField : string)) =
      match x with
      | NoTable -> Simple(Table.Name(tableName))
      | Simple sourceTable -> JoinWith(x, Inner, new Rel(sourceTable, sourceField, Table.Name(tableName), targetField))
      | JoinWith (from, joinType, rel) -> from.InnerJoin(tableName, sourceField, targetField)
    member x.InnerJoinT ((table : Table), (sourceField : string), (targetField : string)) =
      match x with
      | NoTable -> Simple(table)
      | Simple sourceTable -> JoinWith(x, Inner, new Rel(sourceTable, sourceField, table, targetField))
      | JoinWith (from, joinType, rel) -> from.InnerJoinT(table, sourceField, targetField)
    member x.LeftJoin (relationship : Rel) = JoinWith(x, Left, relationship)
    member x.LeftJoin ((tableName : string), (sourceField : string), (targetField : string)) =
      match x with
      | NoTable -> Simple(Table.Name(tableName))
      | Simple sourceTable -> JoinWith(x, Inner, new Rel(sourceTable, sourceField, Table.Name(tableName), targetField))
      | JoinWith (from, joinType, rel) -> from.InnerJoin(tableName, sourceField, targetField)
    member x.LeftJoinT ((table : Table), (sourceField : string), (targetField : string)) =
      match x with
      | NoTable -> Simple(table)
      | Simple sourceTable -> JoinWith(x, Inner, new Rel(sourceTable, sourceField, table, targetField))
      | JoinWith (from, joinType, rel) -> from.InnerJoinT(table, sourceField, targetField)
    member x.RightJoin (relationship : Rel) = JoinWith(x, Right, relationship)
    member x.RightJoin ((tableName : string), (sourceField : string), (targetField : string)) =
      match x with
      | NoTable -> Simple(Table.Name(tableName))
      | Simple sourceTable -> JoinWith(x, Inner, new Rel(sourceTable, sourceField, Table.Name(tableName), targetField))
      | JoinWith (from, joinType, rel) -> from.InnerJoin(tableName, sourceField, targetField)
    member x.RightJoinT ((table : Table), (sourceField : string), (targetField : string)) =
      match x with
      | NoTable -> Simple(table)
      | Simple sourceTable -> JoinWith(x, Inner, new Rel(sourceTable, sourceField, table, targetField))
      | JoinWith (from, joinType, rel) -> from.InnerJoinT(table, sourceField, targetField)
    member x.FullJoin (relationship : Rel) = JoinWith(x, Full, relationship)
    member x.FullJoin ((tableName : string), (sourceField : string), (targetField : string)) =
      match x with
      | NoTable -> Simple(Table.Name(tableName))
      | Simple sourceTable -> JoinWith(x, Inner, new Rel(sourceTable, sourceField, Table.Name(tableName), targetField))
      | JoinWith (from, joinType, rel) -> from.InnerJoin(tableName, sourceField, targetField)
    member x.FullJoinT ((table : Table), (sourceField : string), (targetField : string)) =
      match x with
      | NoTable -> Simple(table)
      | Simple sourceTable -> JoinWith(x, Inner, new Rel(sourceTable, sourceField, table, targetField))
      | JoinWith (from, joinType, rel) -> from.InnerJoinT(table, sourceField, targetField)
    static member TableName (tableName : string) = Simple(Table.Name(tableName))
    static member TableNameAs ((tableName : string), (alias : string)) = Simple(Table.NameAs(tableName, alias))
    static member QualifiedTableName ((database : string), (owner : string), (tableName : string)) = Simple(Table.QualifiedName(database, owner, tableName))
    static member QualifiedTableNameAs ((database : string), (owner : string), (tableName : string), (alias : string)) =
        Simple(Table.QualifiedNameAs(database, owner, tableName, alias))
  end

type Field = 
  | Literal of Table option * string * string option
  | CalcExpression of string * string option
  | Subquery of Field * From * Where * string option
  | FieldString of string
  with
    static member Name (fieldName : string) = Literal(None, fieldName, None)
    static member NameAs ((fieldName : string), (alias : string)) = Literal(None, fieldName, Some(alias))
    static member TableName ((table : Table), (fieldName : string)) = Literal(Some(table), fieldName, None)
    static member TablenameName ((table : string), (fieldName : string)) = Literal(Some(Table.Name(table)), fieldName, None)
    static member TableNameAs ((table : Table), (fieldName : string), (alias : string)) = Literal(Some(table), fieldName, Some(alias))
    static member TablenameNameAs ((table : string), (fieldName : string), (alias : string)) = Literal(Some(Table.Name(table)), fieldName, Some(alias))
    static member Calc (expression : string) = CalcExpression(expression, None)
    static member CalcAs ((expression : string), (alias : string)) = CalcExpression(expression, Some(alias))
  end

type Order =
  | Ascending
  | Descending

type Quantifier =
  | Any
  | Distinct
  | Top of int
  | TopPercent of int

type OrderBy = struct
    val field : string
    val order : Order
    new(fieldname, fieldorder) = 
        { field = fieldname;
          order = fieldorder; }
end

type GroupSplitType = 
  | NoSplit
  | Separate
  | Go

type Statement = 
  | Select of Field array * Quantifier * From * Where * string array * Where * OrderBy array
  | InsertVals of Table * (string * string) array
  | InsertSel of (string * Field) array * Table * Quantifier * From * Where
  | Update of Table * (string * string) array * From option * Where
  | Delete of From * Where
  | StoredProcedure of string * Val array
  | StatementGroup of Statement list * GroupSplitType * bool
with
    static member FieldsFromStrings (fields : string array) = Seq.to_array (Seq.map (fun s -> FieldString(s)) fields)
    static member TransformTuple (fields : (string * string) array) = Seq.to_array (Seq.map (fun (a,b) -> (a,FieldString(b))) fields)
    static member SimpleSel ((fields : string array), (from : From), (where : Where)) = Select((Statement.FieldsFromStrings fields), Any, from, where, [| |], All, [| |])
    static member SimpleSelF ((fields : Field array), (from : From), (where : Where)) = Select(fields, Any, from, where, [| |], All, [| |])
    static member FullSel ((fields : string array), (quantifier : Quantifier), (from : From), (where : Where), (orderBy : OrderBy array)) =
        Select((Statement.FieldsFromStrings fields), quantifier, from, where, [| |], All, orderBy)
    static member FullSelF ((fields : Field array), (quantifier : Quantifier), (from : From), (where : Where), (orderBy : OrderBy array)) =
        Select(fields, quantifier, from, where, [| |], All, orderBy)
    static member GroupSel ((fields : string array), (from : From), (where : Where), (groupBy : string array), (having : Where), (orderBy : OrderBy array)) =
        Select((Statement.FieldsFromStrings fields), Any, from, where, groupBy, having, orderBy)
    static member GroupSelF ((fields : Field array), (from : From), (where : Where), (groupBy : string array), (having : Where), (orderBy : OrderBy array)) =
        Select(fields, Any, from, where, groupBy, having, orderBy)
    static member InsertFromSel ((fields : string array), (table : Table), (selectStatement : Statement)) =
      match selectStatement with
      | Select (selectFields, quant, from, where, _, _, _) ->
          let allFields = List.to_array (util.zip (Array.to_list fields) (Array.to_list selectFields))
          InsertSel(allFields, table, quant, from, where)
      | _ -> raise (IllegalStatementType "Illegal statement type in insertFromSel")
    static member InsertSel ((fields : (string * string) array), (table : Table), (from : From), (where : Where)) =
        InsertSel((Statement.TransformTuple fields), table, Any, from, where)
    static member InsertSelF ((fields : (string * Field) array), (table : Table), (from : From), (where : Where)) =
        InsertSel(fields, table, Any, from, where)
    static member SimpleUpdate ((table : Table), (setFields : (string * string) array), (where : Where)) =
        Update(table, setFields, None, where)
    static member FullUpdate ((table : Table), (setFields : (string * string) array), (from : From), (where : Where)) =
        Update(table, setFields, Some(from), where)
    static member Group ((statements : Statement array), (splitType: GroupSplitType), (useTransaction: bool)) =
        StatementGroup((Array.to_list statements), splitType, useTransaction)
end
  
module SqlModel = begin
  let ( &: ) s1 s2 = And(s1,s2)
  let ( |: ) s1 s2 = Or(s1,s2)

  let ( +: ) from rel = JoinWith(from, Inner, rel)

  let extractJoin (join : Rel) =
    (join.parentTable, join.parentField, join.childTable, join.childField)

  let extractOrderBy (orderby : OrderBy) =
    (orderby.field, orderby.order)

  let arr_to_cdl arr = 
    string.Join(",",Array.map(fun a -> Printf.sprintf "[%s]" a) arr)
  let seq_to_bracket seq =
    Seq.map (fun a -> Printf.sprintf "[%s]" a) seq
  let seq_to_cdl seq =
    Seq.fold1 (fun a b -> Printf.sprintf "%s, %s" a b) seq
  let first_to_bracket_cdl tlst =
    Seq.map (fun (a,_) -> a) tlst |> seq_to_bracket |> seq_to_cdl
  let first_to_cdl tlst =
    Seq.map (fun (a,_) -> a) tlst |> seq_to_cdl
  let second_to_cdl tlst =
    Seq.map (fun (_,b) -> b) tlst |> seq_to_cdl

  let rec IdentRegex str =
    let sqlIdentifier = new Regex(@"^([\w_]+)$") 
    let sqlMatch = sqlIdentifier.Match(str) 
    let success = sqlMatch.Success
    match success with 
    | true -> Printf.sprintf "[%s]" (sqlMatch.Groups.Item(1).ToString())
    | false -> Printf.sprintf "%s" str  
  and TableRegex str =
    let sqlTableDot = new Regex(@"^([\w_]+)\.([\w_]+)$")
    let sqlMatch = sqlTableDot.Match(str)
    let success = sqlMatch.Success
    match success with 
    | true -> Printf.sprintf "[%s].[%s]" (sqlMatch.Groups.Item(1).ToString()) (sqlMatch.Groups.Item(2).ToString())
    | false -> IdentRegex str  
  and AsRegex field = 
    let sqlAs = new Regex(@"^(.+)\s+as\s+([\w_]+)$")
    let sqlMatch = sqlAs.Match(field) 
    let success = (sqlMatch.Success)
    match success with
    | true -> Printf.sprintf "%s as [%s]" (AsRegex (sqlMatch.Groups.Item(1).ToString())) (sqlMatch.Groups.Item(2).ToString())
    | false -> TableRegex field 
              
  let parseFieldexpressions arr = 
    string.Join (",",Array.map AsRegex arr)

  let emitTableSql (t : Table) =
    match (t.Database, t.Owner, t.TableName, t.Alias) with
    | (None, None, name, None) -> Printf.sprintf "[%s]" name
    | (Some (db), None, name, None) -> Printf.sprintf "[%s].dbo.[%s]" db name
    | (None, Some (owner), name, None) -> Printf.sprintf "[%s].[%s]" owner name
    | (Some (db), Some (owner), name, None) -> Printf.sprintf "[%s].[%s].[%s]" db owner name
    | (None, None, name, Some(alias)) -> Printf.sprintf "[%s] as %s" name alias
    | (Some (db), None, name, Some(alias)) -> Printf.sprintf "[%s].dbo.[%s] as %s" db name alias
    | (None, Some (owner), name, Some(alias)) -> Printf.sprintf "[%s].[%s] as %s" owner name alias
    | (Some (db), Some (owner), name, Some(alias)) -> Printf.sprintf "[%s].[%s].[%s] as %s" db owner name alias

  let emitFieldTableSql (t : Table) =
    match (t.Database, t.Owner, t.TableName, t.Alias) with
    | (None, None, name, None) -> Printf.sprintf "[%s]" name
    | (Some (db), None, name, None) -> Printf.sprintf "[%s].dbo.[%s]" db name
    | (None, Some (owner), name, None) -> Printf.sprintf "[%s].[%s]" owner name
    | (Some (db), Some (owner), name, None) -> Printf.sprintf "[%s].[%s].[%s]" db owner name
    | (None, None, name, Some(alias)) -> Printf.sprintf "%s" alias
    | (Some (db), None, name, Some(alias)) -> Printf.sprintf "%s" alias
    | (None, Some (owner), name, Some(alias)) -> Printf.sprintf "%s" alias
    | (Some (db), Some (owner), name, Some(alias)) -> Printf.sprintf "%s" alias

  let escapeSqlString (str : string) = str.Replace("'", "''")

  let emitValSql v =
    match v with
    | Field(t,fieldName) ->
      match t with
      | None -> Printf.sprintf "[%s]" fieldName
      | Some(tableName) -> Printf.sprintf "[%s].[%s]" tableName fieldName
    | Parameter(param) -> Printf.sprintf "@%s" param
    | ValExpression(expr) -> Printf.sprintf "%s" expr
    | StringLiteral(lit) -> Printf.sprintf "'%s'" (escapeSqlString lit)
    | IntLiteral(lit) -> Printf.sprintf "%d" lit
    | FloatLiteral(lit) -> Printf.sprintf "%f" lit
    | BoolLiteral(lit) ->
        match lit with true -> "1" | false -> "0"
    | DateLiteral(lit) -> Printf.sprintf "'%s'" (lit.ToShortDateString())
    | DateTimeLiteral(lit) -> Printf.sprintf "'%s'" (lit.ToString())

  let rec emitSelectionSql sel = 
    match sel with
    | All -> "1=1"
    | FieldValueList (fieldname, values) ->
        Printf.sprintf "[%s] in (%s)" fieldname (seq_to_cdl (Seq.map (fun v -> string_of_int v) values))
    | SubQueryIn (join,insel) ->
        let (pt, pf, ct, cf) = extractJoin join
        if insel = All
          then Printf.sprintf "[%s] in (select [%s] from %s)" pf cf (emitTableSql ct)
          else Printf.sprintf "[%s] in (select [%s] from %s where %s)" pf cf (emitTableSql ct) (emitSelectionSql insel)
    | SubQueryExists (join,insel) ->
        let (pt, pf, ct, cf) = extractJoin join
        if insel = All
          then Printf.sprintf " exists (select * from %s where %s.[%s] = %s.[%s])" (emitTableSql ct) (emitFieldTableSql pt) pf (emitFieldTableSql ct) cf
          else Printf.sprintf " exists (select * from %s where %s.[%s] = %s.[%s] and %s)" (emitTableSql ct) (emitFieldTableSql pt) pf (emitFieldTableSql ct) cf (emitSelectionSql insel)
    | And (sel1, sel2) ->
        if sel1 = All
          then emitSelectionSql sel2
          else 
            if sel2 = All
              then emitSelectionSql sel1
              else Printf.sprintf "(%s) and (%s)" (emitSelectionSql sel1) (emitSelectionSql sel2)
    | Or (sel1, sel2) ->
        if sel1 = All
          then emitSelectionSql sel1 // always true -- this only simplifies it one level, but every bit helps
          else 
            if sel2 = All
              then emitSelectionSql sel2 // always true -- this only simplifies it one level, but every bit helps
              else Printf.sprintf "(%s) or (%s)" (emitSelectionSql sel1) (emitSelectionSql sel2)
    | Comparison (v1, c) -> 
        match c with
        | Equals(v2) -> Printf.sprintf "%s = %s" (emitValSql v1) (emitValSql v2)
        | NotEqual(v2) -> Printf.sprintf "%s <> %s" (emitValSql v1) (emitValSql v2)
        | LessThan(v2) -> Printf.sprintf "%s < %s" (emitValSql v1) (emitValSql v2)
        | LessThanOrEqual(v2) -> Printf.sprintf "%s <= %s" (emitValSql v1) (emitValSql v2)
        | GreaterThan(v2) -> Printf.sprintf "%s > %s" (emitValSql v1) (emitValSql v2)
        | GreaterThanOrEqual(v2) -> Printf.sprintf "%s >= %s" (emitValSql v1) (emitValSql v2)
        | Like(v2) -> Printf.sprintf "%s like %s" (emitValSql v1) (emitValSql v2)
        | IsNull -> Printf.sprintf "%s is null" (emitValSql v1)
        | IsNotNull -> Printf.sprintf "%s is not null" (emitValSql v1)
    | SelExpression s -> s
    | Not (sel) ->
        Printf.sprintf "not (%s)" (emitSelectionSql sel)

  let emitJoinTypeSql joinType =
    match joinType with
    | Inner -> " inner join "
    | Left  -> " left join "
    | Right -> " right join "
    | Full  -> " full join "

  let emitQuantifierSql quantifier =
    match quantifier with
    | Any -> ""
    | Distinct -> "distinct "
    | Top (i) ->
        Printf.sprintf "top %i " i
    | TopPercent (i) ->
        Printf.sprintf "top %i percent " i

  let rec emitFromSql (from : From) =
    match from with
    | NoTable -> ""
    | Simple t -> Printf.sprintf " from %s" (emitTableSql t)
    | JoinWith (ifrm, joinType, join) ->
        let (pt, pf, ct, cf) = extractJoin join
        Printf.sprintf "%s%s%s on %s.[%s] = %s.[%s]" (emitFromSql ifrm) (emitJoinTypeSql joinType) (emitTableSql ct) (emitFieldTableSql pt) pf (emitFieldTableSql ct) cf

  let emitGroupByListSql (groupbylist : string array) (having : Where) =
    if groupbylist.Length = 0
      then ""
      else 
        if having = Where.All
          then Printf.sprintf " group by %s" (parseFieldexpressions groupbylist)
          else Printf.sprintf " group by %s having %s" (parseFieldexpressions groupbylist) (emitSelectionSql having)

  let emitOrderSql orderType =
    match orderType with
    | Ascending -> " asc "
    | Descending  -> " desc "

  let emitOrderBySql orderBy =
    let (field, order) = extractOrderBy orderBy
    Printf.sprintf "%s%s" field (emitOrderSql order)

  let emitOrderByListSql (orderbylist : OrderBy array) =
    if orderbylist.Length = 0
      then ""
      else Printf.sprintf " order by %s" (seq_to_cdl (Seq.map emitOrderBySql orderbylist))

  let rec emitFieldSql f =
    match f with
    | Literal (tableNameOpt, fieldName, aliasOpt) ->
      match (tableNameOpt, aliasOpt) with
      | (None, None) -> Printf.sprintf "[%s]" fieldName
      | (Some(table), None) -> Printf.sprintf "[%s].[%s]" (emitFieldTableSql table) fieldName
      | (None, Some(alias)) -> Printf.sprintf "[%s] as %s" fieldName alias
      | (Some(table), Some(alias)) -> Printf.sprintf "[%s].[%s] as %s" (emitFieldTableSql table) fieldName alias
    | CalcExpression (expression, aliasOpt) ->
      match aliasOpt with
      | None -> Printf.sprintf "[%s]" expression
      | Some(alias) -> Printf.sprintf "[%s] as %s" expression alias
    | Subquery (field, from, where, aliasOpt) ->
      match aliasOpt with
      | None -> Printf.sprintf "(%s)" (emitSelectSql Quantifier.Any (emitFieldSql field) from where)
      | Some(alias) -> Printf.sprintf "(%s) as %s" (emitSelectSql Quantifier.Any (emitFieldSql field) from where) alias
    | FieldString (parseString) -> AsRegex parseString
  and emitSelectSql quantifier fields from sel =
    if from = NoTable
      then Printf.sprintf "select %s" fields
      else
        if sel = Where.All
          then Printf.sprintf "select %s%s%s" (emitQuantifierSql quantifier) fields (emitFromSql from)
          else Printf.sprintf "select %s%s%s where %s" (emitQuantifierSql quantifier) fields (emitFromSql from) (emitSelectionSql sel)

  let rec emitFieldsSql arr = string.Join(",", (Array.map emitFieldSql arr))

  let rec emitStatementSql stmt =
    match stmt with
    | Select (fields,quantifier,from,sel,groupby,having,orderby) -> 
        Printf.sprintf "%s%s%s" (emitSelectSql quantifier (emitFieldsSql fields) from sel) (emitGroupByListSql groupby having) (emitOrderByListSql orderby)
    | InsertVals (table,kvlist) -> 
        Printf.sprintf "insert into %s (%s) values (%s)" (emitTableSql table) (first_to_bracket_cdl kvlist) (second_to_cdl kvlist)
    | InsertSel (fflist,table,quantifier,from,sel) ->
        Printf.sprintf "insert into %s (%s) %s"
            (emitTableSql table)
            (first_to_bracket_cdl fflist)
            (emitSelectSql quantifier (string.Join(",", (Array.map (fun (_,b) -> emitFieldSql b) fflist))) from sel)
    | Update (table,kvlist,fromOpt,sel) ->
        let setlist = Seq.map (fun (k,v) -> k + " = " + v) kvlist
        if sel = Where.All
          then
            match fromOpt with 
            | None -> Printf.sprintf "update %s set %s" (emitTableSql table) (seq_to_cdl setlist)
            | Some(from) -> Printf.sprintf "update %s%s set %s" (emitTableSql table) (emitFromSql from) (seq_to_cdl setlist)
          else 
            match fromOpt with 
            | None -> Printf.sprintf "update %s set %s where %s" (emitTableSql table) (seq_to_cdl setlist) (emitSelectionSql sel)
            | Some(from) -> Printf.sprintf "update %s%s set %s where %s" (emitTableSql table) (emitFromSql from) (seq_to_cdl setlist) (emitSelectionSql sel)
    | Delete (from,sel) ->
        if sel = Where.All
          then Printf.sprintf "delete%s" (emitFromSql from)
          else Printf.sprintf "delete%s where %s" (emitFromSql from) (emitSelectionSql sel)  
    | StoredProcedure (procName, vals) ->
        Printf.sprintf "EXEC %s %s" procName (arr_to_cdl (Array.map emitValSql vals))
    | StatementGroup (statements, splitType, useTran) ->
        match splitType with
        | NoSplit -> List.fold_right (fun a b -> Printf.sprintf "%s\n\n%s" a b) (List.map (fun x -> emitStatementSql x) statements) ""
        | _ -> List.fold_right (fun a b -> Printf.sprintf "%s\ngo\n\n%s" a b) (List.map (fun x -> emitStatementSql x) statements) ""

  let rec emitPartialSql stmt =
    match stmt with
    | StatementGroup (statements, splitType, useTran) ->
        match splitType with
        | NoSplit -> (List.fold_right (fun a b -> Printf.sprintf "%s\n\n%s" a b) (List.map (fun x -> emitStatementSql x) statements) "", None)
        | Separate ->
            match statements with
            | [] -> ("", None)
            | x::xs ->
                       let (s,o) = emitPartialSql x
                       match o with
                       | None -> (s, Some(StatementGroup(xs, splitType, useTran)))
                       | Some(p) -> (s, Some(StatementGroup(p::xs, splitType, useTran)))
        | Go -> (List.fold_right (fun a b -> Printf.sprintf "%s\ngo\n\n%s" a b) (List.map (fun x -> emitStatementSql x) statements) "", None)
    | x -> (emitStatementSql x, None)
end
